﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Data;
using System.Data.Common;
using System.Text.RegularExpressions;
using Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using ChBusiness.Struct;
using System.Net;
using ChBusiness.Common;

namespace ChBusiness.Wsbs
{
    /// <summary>
    /// 外网申报信息提交类
    /// </summary>
    public class ShenBaoSubmit
    {
        Database dbTemp = BasicOperate.CreateNetOfficeDataBase();

        /// <summary>
        /// 添加外网提交
        /// </summary>
        /// <param name="strIId">办件流水号</param>
        /// <param name="strFlowName">流程名称</param>
        /// <param name="strRequester">申请人</param>
        /// <returns></returns>
        public int InsertSubmitInfo(string strIId, string strFlowName, string strRequester)
        {
            //获取当前发布流程的信息 addby zhongjian 20091022
            string strFlowID = string.Empty;
            string strFlowType = string.Empty;
            DataTable dtTemp = GetFlowDefine(strFlowName);
            if (dtTemp.Rows.Count > 0)
            {
                strFlowID = dtTemp.Rows[0]["id"].ToString();
                strFlowType = dtTemp.Rows[0]["flowtype"].ToString();
            }
            //判断有无记录
            string strSql = string.Format("select iid from XT_SUBMIT_INFO where iid='{0}'",
                strIId);
            object objResult = dbTemp.ExecuteScalar(CommandType.Text, strSql);
            if (objResult != null)
            {
                if (!string.IsNullOrEmpty(objResult.ToString()))
                {
                    strSql = string.Format(@"update XT_SUBMIT_INFO set FLOWNAME='{4}',FLOWTYPE='{1}',
                            REQUESTER='{2}',SUBMITFLAG=0,edittime=sysdate,flowid='{3}' 
                            where iid='{0}'", strIId, strFlowType, strRequester, strFlowID,strFlowName);
                }
                else
                {
                    strSql = string.Format(@"Insert into XT_SUBMIT_INFO(IID,FLOWNAME,REQUESTER,SUBMITFLAG,
                        flowid,FLOWTYPE) values('{0}','{1}','{2}',0,'{3}','{4}')",
                        strIId, strFlowName, strRequester, strFlowID, strFlowType);
                }
            }
            else
            {
                strSql = string.Format(@"Insert into XT_SUBMIT_INFO(IID,FLOWNAME,REQUESTER,SUBMITFLAG,flowid,
                    FLOWTYPE) values('{0}','{1}','{2}',0,'{3}','{4}')",
                    strIId, strFlowName, strRequester, strFlowID, strFlowType);                
            }

            //添加操作日志 addby zhongjian 20100401
            string strRemark = string.Format("申请 {0} 的业务,申请编号为: {1}", strFlowType, strIId);
            SystemLogs.AddSystemLogs(strRequester, "add", strRemark, strSql);

            return dbTemp.ExecuteNonQuery(CommandType.Text, strSql);
        }

        /// <summary>
        /// 判断是否受理成功
        /// </summary>
        /// <param name="strNewId"></param>
        /// <returns></returns>
        /// <!--update by zhongjian 20100312 修改从xt_submit_info表中验证 -->
        public bool IsShouLi(string strNewId)
        {
            string strSql = string.Empty;
            strSql = string.Format(@"select iid from xt_submit_info t where submitflag='1' and iid='{0}'", strNewId);
            object objResult = dbTemp.ExecuteScalar(CommandType.Text, strSql);
            if (objResult != null)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 拷贝并新增
        /// </summary>
        /// <param name="strIID"></param>
        public string CopyAndAddShenBao(string strIID, string strFid,string strFlowName)
        {
            OracleSystemOperation osoTemp = new OracleSystemOperation();
            FlowOperation.SerialInstanceOperation sioTemp = new FlowOperation.SerialInstanceOperation();
            long newIID = sioTemp.GetIIDWithEnterprise("", "", "");
            string strSql = string.Empty;
            DataTable dtTbName = new DataTable();
            if (newIID != 0)
            {
                if( strFlowName.IndexOf("地图审核")==-1 && !string.IsNullOrEmpty(strFid))
                {
                    string strSqlTbName = string.Format("select distinct a.db_table from st_form_input a where fid='{0}'", strFid);
                    dtTbName = dbTemp.ExecuteDataSet(CommandType.Text, strSqlTbName).Tables[0];
                    if (dtTbName.Rows.Count > 0)
                    {
                        for (int i = 0; i < dtTbName.Rows.Count; i++)
                        {
                            osoTemp.GetNewTaskSql(newIID.ToString(), strIID, dtTbName.Rows[i]["db_table"].ToString());
                        }
                    }
                }
                else if (strFlowName.IndexOf("地图审核") >= 0)
                {
                    strSql = string.Format("insert into UT_国家测绘局地图审核申请表(IID,FORM_NAME,INPUT_INDEX,RESOURCE_NAME,单位名称,传真,电话,联系人,地址,邮编,图名,规格,图幅数量,送审单位,版次_初版,版次_再版,原审图号,用途_公开出版,用途_公开展示,用途_公开登载,用途_书刊插图,用途_对外加工,用途_境外引进,用途_礼品赠送,用途_其他,形式_纸质图,形式_电子地图,形式_地球仪,形式_其他产品,所用基本资料名称,原编制者或出版者,使用许可证明文件_有,使用许可证明文件_无,等级证书证号,相关材料证号,地图出版范围的批准文件_有,地图出版范围的批准文件_无,地图出版范围的批准文件_文号,送审函件_有,送审函件_无,送审函件_文号,批准文件_有,批准文件_无,批准文件_文号,核准文件_有,核准文件_无,核准文件_文号,保密技术处理的证明文件_有,保密技术处理的证明文件_无,保密技术处理的证明文件_文号,保密部门审查的证明文件_有,保密部门审查的证明文件_无,保密部门审查的证明文件_文号,境外引进的地图材料_有,境外引进的地图材料_无,其他书面说明材料_有,其他书面说明材料_无,样图及材料是否齐全,应急保障类地图_有,应急保障类地图_无,协助审查_有,协助审查_无,宣传图_有,宣传图_无, 城市数量,申请次数,用途_软件开发,用途_测试,保密技术处理证明文件_有,保密技术处理证明文件_无,保密技术处理证明文件_文号,增量更新内容说明材料_有,增量更新内容说明材料_无,产品名称) select '{0}',FORM_NAME,INPUT_INDEX,RESOURCE_NAME,单位名称,传真,电话,联系人,地址,邮编,图名,规格,图幅数量,送审单位,版次_初版,版次_再版,原审图号,用途_公开出版,用途_公开展示,用途_公开登载,用途_书刊插图,用途_对外加工,用途_境外引进,用途_礼品赠送,用途_其他,形式_纸质图,形式_电子地图,形式_地球仪,形式_其他产品,所用基本资料名称,原编制者或出版者,使用许可证明文件_有,使用许可证明文件_无,等级证书证号,相关材料证号,地图出版范围的批准文件_有,地图出版范围的批准文件_无,地图出版范围的批准文件_文号,送审函件_有,送审函件_无,送审函件_文号,批准文件_有,批准文件_无,批准文件_文号,核准文件_有,核准文件_无,核准文件_文号,保密技术处理的证明文件_有,保密技术处理的证明文件_无,保密技术处理的证明文件_文号,保密部门审查的证明文件_有,保密部门审查的证明文件_无,保密部门审查的证明文件_文号,境外引进的地图材料_有,境外引进的地图材料_无,其他书面说明材料_有,其他书面说明材料_无,样图及材料是否齐全,应急保障类地图_有,应急保障类地图_无,协助审查_有,协助审查_无,宣传图_有,宣传图_无,城市数量,申请次数,用途_软件开发,用途_测试,保密技术处理证明文件_有,保密技术处理证明文件_无,保密技术处理证明文件_文号,增量更新内容说明材料_有,增量更新内容说明材料_无,产品名称  from UT_国家测绘局地图审核申请表 t where iid='{1}'", newIID, strIID);
                    dbTemp.ExecuteNonQuery(CommandType.Text, strSql);
                }
                return newIID.ToString();

            }
            else
            {
                return null;
            }

        }
       
        /// <summary>
        /// 修改提交标志
        /// </summary>
        /// <param name="strIId">办件流水号</param>
        /// <param name="nFlag">提交标识符</param>
        /// <returns></returns>
        /// <!--修改提交时间为当前时间 update by zhongjian 20100131-->
        public int UpdateSubmit(string strIId, int nFlag)
        {
            string strSql = string.Format(@"update XT_SUBMIT_INFO set SUBMITFLAG={1}, edittime=sysdate where IID='{0}'", strIId, nFlag);

            return dbTemp.ExecuteNonQuery(CommandType.Text, strSql);
        }

        /// <summary>
        /// 获取尚未提交事项
        /// </summary>
        /// <param name="strUserId">用户id</param>
        /// <param name="strIID">办件编号</param>
        /// <param name="strName">申请人</param>
        /// <returns></returns>
        public DataTable GetNoSubmit(string strUserId,string strIID,string strName)
        {
            string strNoSubmitDay = SystemConfig.NoSubmitDay;
            string strSql = string.Format(@"select IID,FLOWNAME,flowtype,flowid,
                REQUESTER,SUBMITFLAG,to_char(INPUTDATE,'yyyy-mm-dd') INPUTDATE,remark,
                to_char(edittime,'yyyy-mm-dd') edittime,
      (select 图名
          from ut_国家测绘局地图审核申请表 a
         where t.iid = a.iid
           and input_index = 0 and rownum<2) tm,
       (select slbh 
          from ut_地图审核受理通知书 a
         where t.iid = a.iid
           and input_index = 0
           and rownum < 2) slh,
                to_char(floor((edittime+'{1}')-sysdate))||'天'
                ||to_char(mod(TRUNC(((edittime+'{1}')-sysdate)*24),24))||'小时' FINISH_TIME
                 from xt_submit_info t
                where submitflag=0 and isdelete<>'1' and requester='{0}'", strUserId, strNoSubmitDay);

            if (!string.IsNullOrEmpty(strIID))
            {
                strSql += string.Format(" and IID like '%{0}%'",strIID);
            }
            if (!string.IsNullOrEmpty(strName))
            {
                strSql += string.Format(" and REQUESTER like '%{0}%'", strName);
            }
            strSql += " order by iid desc";

            return dbTemp.ExecuteDataSet(CommandType.Text, strSql).Tables[0];
        }

        /// <summary>
        /// 获取尚未提交事项个数
        /// </summary>
        /// <param name="strUserId">用户id</param>
        /// <returns></returns>
        public string GetNoSubmitCount(string strUserId)
        {
            string strSql = string.Format(@"select count(*) from xt_submit_info 
                where submitflag=0 and isdelete=0 and requester='{0}'",strUserId);
            DbCommand dbCmd = dbTemp.GetSqlStringCommand(strSql);
            object result = dbTemp.ExecuteScalar(dbCmd);
            if (result != null)
            {
                return result.ToString();
            }
            else
            {
                return string.Empty;
            }
        }

        /// <summary>
        /// 获取补正补齐办理事项
        /// </summary>
        /// <param name="strUserId">用户id</param>
        /// <param name="strIID">办件编号</param>
        /// <param name="strName">申请人</param>
        /// <returns></returns>
        public DataTable GetBuZhengSubmit(string strUserId,string strIID,string strName)
        {
            string strBuZhengSubmitDay= SystemConfig.BuZhengSubmitDay;
            string strSql = string.Format(@"select IID,FLOWNAME,flowid,flowtype,
       (select 图名
          from ut_国家测绘局地图审核申请表 a
         where t.iid = a.iid
           and input_index = 0 and rownum<2) tm,
       (select slbh 
          from ut_地图审核受理通知书 a
         where t.iid = a.iid
           and input_index = 0
           and rownum < 2) slh,
       REQUESTER,
       to_char(INPUTDATE, 'yyyy-mm-dd') INPUTDATE,
       SUBMITFLAG,
       remark,
       to_char(edittime, 'yyyy-mm-dd') edittime,
       to_char(floor((edittime + '{1}') - sysdate)) || '天' ||
       to_char(mod(TRUNC(((edittime + '{1}') - sysdate) * 24), 24)) || '小时' as FINISH_TIME
  from xt_submit_info t
 where submitflag = -1
   and isdelete <> '1'
   and requester = '{0}'", strUserId, strBuZhengSubmitDay);

            if (!string.IsNullOrEmpty(strIID))
            {
                strSql += string.Format(" and IID like '%{0}%'", strIID);
            }
            if (!string.IsNullOrEmpty(strName))
            {
                strSql += string.Format(" and REQUESTER like '%{0}%'", strName);
            }
            strSql += " order by iid desc";

            return dbTemp.ExecuteDataSet(CommandType.Text, strSql).Tables[0];
        }

        /// <summary>
        /// 获取补正补齐办理事项个数
        /// </summary>
        /// <param name="strUserId">用户id</param>
        /// <returns></returns>
        public string GetBuZhengSubmitCount(string strUserId)
        {
            string strSql = string.Format(@"select count(*) from xt_submit_info 
                where submitflag=-1 and isdelete<>'1' and requester='{0}'", strUserId);
            DbCommand dbCmd = dbTemp.GetSqlStringCommand(strSql);
            object result = dbTemp.ExecuteScalar(dbCmd);
            if (result != null)
            {
                return result.ToString();
            }
            else
            {
                return string.Empty;
            }
        }

        /// <summary>
        /// 获取iid
        /// </summary>
        /// <param name="strIId">办件流水号</param>
        /// <returns></returns>
        public string GetIId(string strIId)
        {
            OracleSystemOperation csoTemp = new OracleSystemOperation();
            return csoTemp.GetTableFieldValue("UT_测绘成果使用申请表", "iid", "iid='" + strIId + "'");
        }

        /// <summary>
        /// 获取是否允许校验标识
        /// </summary>
        /// <param name="strFlowname">行政许可事项</param>
        /// <returns></returns>
        public string GetFlag(string strFlowname)
        {
            OracleSystemOperation csoTemp = new OracleSystemOperation();
            return csoTemp.GetTableFieldValue("xt_workflow_define", "ischeck", "flowname='" + strFlowname + "'");
        }

        /// <summary>
        /// 获取必填项
        /// </summary>
        /// <param name="strFlowname"></param>
        /// <param name="strIID"></param>
        /// <returns></returns>
        public string GetBtx(string strFlowname,string strIID)
        {
            string strSql = string.Format(@"select * from UT_SMCGSY_BTPZ t
                where flowname='{0}'", strFlowname);
            DataTable dtReturn = dbTemp.ExecuteDataSet(CommandType.Text, strSql).Tables[0];
            string strReturn = "";

            foreach(DataRow drTemp in dtReturn.Rows)
            {
                string strTable = drTemp["tablename"].ToString();
                string strWhere = drTemp["querywhere"].ToString();
                strWhere = string.Format(" iid='{0}' and ({1})",strIID,strWhere);

                OracleSystemOperation csoTemp = new OracleSystemOperation();
                string strCount = csoTemp.GetTableFieldValue(strTable, "count(1) cn", strWhere);
                if(Convert.ToInt32(strCount)>0)
                {
                    strReturn = "存在未填写的必填项，请填报";
                }
            }
            return strReturn;            
        }

        /// <summary>
        /// 获取提交标志
        /// </summary>
        /// <param name="strIId">办件流水号</param>
        /// <returns></returns>
        public string GetIsSubmit(string strIId)
        {
            OracleSystemOperation csoTemp = new OracleSystemOperation();
            return csoTemp.GetTableFieldValue("XT_SUBMIT_INFO", "SUBMITFLAG", "IID='" + strIId + "'");
        }

        /// <summary>
        /// 获取在线办理提交标志
        /// </summary>
        /// <param name="strIId">办件流水号</param>
        /// <param name="nFlag">提交标志位</param>
        /// <returns></returns>
        public string GetZsblSubmitFlag(string strIId, string nFlag)
        {
            string strSql = string.Empty;
            switch (nFlag)
            {
                case "1"://变更
                    strSql = string.Format("select IS_BG from XT_SUBMIT_INFO where IID='{0}'", strIId);
                    break;
                case "2"://延期
                    strSql = string.Format("select IS_YQ from XT_SUBMIT_INFO where IID='{0}'", strIId);
                    break;
                case "3"://撤销
                    strSql = string.Format("select IS_CX from XT_SUBMIT_INFO where IID='{0}'", strIId);
                    break;
            }

            if (!string.IsNullOrEmpty(strSql))
            {
                object objResult = dbTemp.ExecuteScalar(CommandType.Text, strSql);
                if (objResult != null)
                {
                    return objResult.ToString();
                }
                else
                {
                    return string.Empty;
                }
            }
            else
            {
                return string.Empty;
            }
        }

        /// <summary>
        /// 更新变更状态位
        /// </summary>
        /// <param name="strIId">办件流水号</param>
        /// <param name="strFlag">标志位</param>
        /// <returns></returns>
        public int SetZsblSubmitFlag(string strIId, string strFlag)
        {
            string strSql = string.Format("update XT_SUBMIT_INFO set IS_BG='2' where IID='{0}'", strIId);
            switch (strFlag)
            {
                case "1"://变更
                    break;
                case "2"://延期
                    strSql = string.Format("update XT_SUBMIT_INFO set IS_YQ='2' where IID='{0}'", strIId);
                    break;
                case "3"://撤销
                    strSql = string.Format("update XT_SUBMIT_INFO set IS_CX='2' where IID='{0}'", strIId);
                    break;
            }

            return dbTemp.ExecuteNonQuery(CommandType.Text, strSql);
        }

        /// <summary>
        /// 根据流名称获取当前发布的流程信息
        /// </summary>
        /// <param name="strFlowName">流程名称</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20091022-->
        public DataTable GetFlowDefine(string strFlowName)
        {
            string strSql = string.Format(@"select id,flowname,flowtype,flownum,ischeck,createtime,edittime,pubtime
                            from xt_workflow_define where ispub=1 and isdelete=0 and flowname='{0}'", strFlowName);
            return dbTemp.ExecuteDataSet(CommandType.Text, strSql).Tables[0];
        }


        /// <summary>
        /// 将尚未提交事项逻辑删除
        /// </summary>
        /// <param name="strIID">办件流水号</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20091218-->
        public bool SetDelete(string strIID)
        {
            string strSql = string.Format(@"update xt_submit_info set isdelete='1' where iid='{0}'", strIID);

            object objResult = dbTemp.ExecuteScalar(CommandType.Text, strSql);
            if (objResult != null)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 获取补正补齐原因
        /// </summary>
        /// <param name="strIId">业务编号</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20091225-->
        public string GetSubmitRemark(string strIId)
        {
            OracleSystemOperation csoTemp = new OracleSystemOperation();
            return csoTemp.GetTableFieldValue("XT_SUBMIT_INFO", "remark", "IID='" + strIId + "'");
        }

        /// <summary>
        /// 获取业务终止原因
        /// </summary>
        /// <param name="strIId">业务编号</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20100316-->
        public string GetSubmitStopRemark(string strIId)
        {
            OracleSystemOperation csoTemp = new OracleSystemOperation();
            return csoTemp.GetTableFieldValue("xt_instance_ext", "stop_remark", "IID='" + strIId + "'");
        }


        /// <summary>
        /// 删除办理事项(包括相关的表单信息和附件内容)
        /// </summary>
        /// <param name="strUserID">用户ID</param>
        /// <param name="strIID">办件编号</param>
        /// <returns></returns>
        public bool DeleteSubmit(string strUserID,string strIID)
        {
            Database dbTemp1 = BasicOperate.CreateNetOfficeDataBase();
            bool bReturn = false;
            using (DbConnection conTemp = dbTemp1.CreateConnection())
            {
                conTemp.Open();
                DbTransaction transTemp = conTemp.BeginTransaction();
                try
                {
                    DbCommand cmdTemp;
                    //获取该流程业务所涉及到的所有表单表名
                    string strSql = string.Format(@"select distinct db_table
                                                      from st_form_input
                                                     where fid in
                                                           (select fid
                                                              from XT_PROCESS t
                                                             where flowid =
                                                                   (select flowid from xt_submit_info where iid = '{0}'))
                                                    ", strIID);
                    DataTable dt = dbTemp1.ExecuteDataSet(transTemp, CommandType.Text, strSql).Tables[0];
                    if (dt.Rows.Count > 0)
                    {
                        //删除所有涉及到该业务的表单信息
                        for (int i = 0; i < dt.Rows.Count; ++i)
                        {
                            string strTableName = dt.Rows[i]["db_table"].ToString();
                            strSql = string.Format(@"delete from {0} where iid = :strid", strTableName);

                            cmdTemp = dbTemp1.GetSqlStringCommand(strSql);
                            dbTemp1.AddInParameter(cmdTemp, "strid", DbType.String, strIID);
                            dbTemp1.ExecuteNonQuery(cmdTemp, transTemp);
                        }
                    }
                    //删除该业务所上传的附件
                    strSql = string.Format(@"delete from xt_serial_attachment where AppDataId = :strid");
                    cmdTemp = dbTemp1.GetSqlStringCommand(strSql);
                    dbTemp1.AddInParameter(cmdTemp, "strid", DbType.String, strIID);
                    dbTemp1.ExecuteNonQuery(cmdTemp, transTemp);

                    //最后删除提交信息表
                    strSql = string.Format(@"delete from xt_submit_info where iid='{0}'", strIID);
                    dbTemp1.ExecuteScalar(transTemp, CommandType.Text, strSql);

                    transTemp.Commit();
                    //添加操作日志 addby zhongjian 20100401
                    string strRemark = string.Format("删除申请编号为: {0} 的业务,包括所有涉及到该业务的表单信息", strIID);
                    SystemLogs.AddSystemLogs(strUserID, "delete", strRemark, strSql);

                    bReturn = true;
                }
                catch
                {
                    transTemp.Rollback();
                    bReturn = false;
                }
            }
            return bReturn;
        }
    }
}
